using Microsoft.AspNetCore.Mvc;
using MySqlX.XDevAPI;
using S7.Net;
using System.Drawing.Printing;
using System.Dynamic;
using ZR.Admin.WebApi.Filters;
using ZR.Model.Dto.Business.ProductionControl;
using ZR.Model.Models.ProductionControl;
using ZR.Service.IService.ProductionControl;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.ListView;
using FreeSql;
using AntdUI;
using System.Collections;
using ZR.Model.Models.Export;
using MiniExcelLibs;
using System.Web;
using Microsoft.IdentityModel.Tokens;
using MiniExcelLibs.Attributes;
using MiniExcelLibs.OpenXml;
using System.Diagnostics;
//创建时间：2024-10-08
namespace ZR.Admin.WebApi.Controllers
{
    /// <summary>
    /// PLC配置表
    /// </summary>
    [Verify]
    [Route("run/plcconfig")]
    [ApiExplorerSettings(GroupName = "business")]
    public class PlcConfigController : BaseController
    {
        /// <summary>
        /// PLC配置表接口
        /// </summary>
        private readonly IPlcConfigService _PlcConfigService;

        public PlcConfigController(IPlcConfigService PlcConfigService)
        {
            _PlcConfigService = PlcConfigService;
        }

        /// <summary>
        /// 查询PLC配置表列表
        /// </summary>
        /// <param name="parm"></param>
        /// <returns></returns>
        [HttpGet("list")]
        [ActionPermissionFilter(Permission = "run:plcconfig:list")]
        public IActionResult QueryPlcConfig([FromQuery] PlcConfigQueryDto parm)
        {
            var response = _PlcConfigService.GetList(parm);
            return SUCCESS(response);
        }

        /// <summary>
        /// 数据追溯查询，返回列头信息 和 表格数据
        /// </summary>
        /// <param name="parm"></param>
        /// <returns></returns>
        [HttpPost("QueryCollectData/{tableName}")]
        [AllowAnonymous]
        public IActionResult QueryCollectData([FromBody] PlcConfigQueryDto parm, [FromRoute] string tableName)
        {
            Stopwatch stopwatch = Stopwatch.StartNew();
            var _fsql = InternalApp.Fsql; 
            using (var ObjectCo = _fsql.Ado.MasterPool.Get())
            {
                var conn = ObjectCo.Value;

                var table = conn.Select<CksVirtualTable>().Where(a => a.TableName == tableName).First();
                if(table == null)
                    return ToResponse(ResultCode.FAIL, "请刷新网页重试");

                int TableId = table.Id;
                var columnQuery = conn.Select<CksVirtualColumn>().Where(a => a.TableId == TableId).OrderBy(a => a.Sequence);
                var columns = columnQuery.ToList();

                //查询虚拟表格数据

                var page =parm.Limit>0 ? 1 :   parm.PageNum;
                var pageSize = parm.Limit > 0 ? parm.Limit : parm.PageSize;

                if (parm.Limit > 10000)
                {
                    return ToResponse(ResultCode.FAIL, "Limit Size 最大 10000");
                }
                // 1. 先获取符合条件的批次数据（按时间范围过滤）
                var batchQuery = conn.Select<CksCollected>()
                    .Where(x => x.TableId == table.Id)
                     .WhereIf(!parm.PrimaryCode.IsNullOrEmpty() && parm.PrimaryCode.Length >= 4, x => x.Suffix_4 == GetSuffix4(parm.PrimaryCode))
                     .WhereIf(!parm.PrimaryCode.IsNullOrEmpty(), x => x.PrimaryCode.EndsWith(parm.PrimaryCode))
                     .WhereIf( parm.Limit>0 , x=> x.SequenceId >= parm.StartSeq)
                    .WhereIf(parm.BeginTime != null, x => x.CreatedTime.Between( parm.BeginTime.Value , parm.EndTime.Value))
                    .OrderByIf(parm.Limit > 0, x => x.SequenceId)
                    .OrderByDescending(parm.Limit == 0, x => x.SequenceId)
                    .Page(page, pageSize)
                    .OrderByDescending(parm.Limit > 0, x => x.SequenceId);
               

                var total = batchQuery.Count();
                // 2. 获取这些批次的所有明细数据（一次性加载）
                var details = conn.Select<CksCollectedDetails>().FromQuery(batchQuery, columnQuery)//conn.Select<CksCollected>().WithMemory(batches))
                            .InnerJoin((a, b,c) => a.TableId == b.TableId && a.BatchSuffix == b.BatchSuffix  && a.BatchCode == b.BatchCode)
                            .InnerJoin((a,b,c) => a.TableId == c.TableId  && a.ColumnId == c.Id )
                           
                            // .Where((a, b, c) => a.TableName == table.TableName)
                            .ToList(
                                (a,b, c) => new  { c.ColumnName,b.CreatedTime ,b.SequenceId ,b.BatchCode,a.Content }
                            ) ;
                //_fsql.Select<CksCollectedDetails>().FromQuery(columnQuery, batchQuery)
                //    .InnerJoin((a, b, c) => a.BatchCode == b.);


                columns.Insert(0, new CksVirtualColumn() { ColumnName = "CreatedTime",Notes="创建时间",Width=160, LanguageKey = "" });
               
                foreach (var item in columns)
                {
                    string lowerColumnName = GetFirstLowerChar(item.ColumnName);
                    item.ColumnName = lowerColumnName;
                }


                //组装数据
                var result = details
                                .Select(d => new {
                                    d.BatchCode,
                                    d.ColumnName,
                                    d.Content,
                                    d.CreatedTime,
                                    d.SequenceId
                                })
                                .GroupBy(x => new { x.BatchCode,x.CreatedTime,x.SequenceId })
                                .Select(g => {
                                    dynamic row = new ExpandoObject();
                                    var dict = (IDictionary<string, object>)row;
                                    dict["batchId"] = g.Key.BatchCode;
                                    dict["sequenceId"] = g.Key.SequenceId;
                                    //  dict["CollectionTime"] = g.Key.CollectionTime;

                                    //遍历列头，为空则赋值空
                                    foreach (var col in columns)
                                    {
                                      var item = g.ToList().Find(a => a.ColumnName.ToLower() == col.ColumnName.ToLower());

                                      dict[col.ColumnName] = item ==null?"": item.Content;
                                    }
                                    dict["createdTime"] = g.Key.CreatedTime;
                                    return row;
                                })
                                .OrderByDescending(x => x.sequenceId)
                                .ToList();
                object rcolumns = null;
                if(parm.Limit>0)
                    rcolumns = columns.Select(a => new { a.ColumnName, a.Notes });
                else
                    rcolumns = columns.Select(a => new { a.ColumnName, a.Notes, a.Width ,a.IsDisplay });
                stopwatch.Stop();
                return SUCCESS(new { queryTime = $"{(int)stopwatch.Elapsed.TotalMilliseconds}ms", data = result  , columns = rcolumns, Pagination =new { Total = total } });


            }

            
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="parm"></param>
        /// <returns></returns>
        [HttpGet("ExportCollectData")]
        public IActionResult ExportCollectData([FromQuery]PlcConfigQueryDto parm)
        {
            var _fsql = InternalApp.Fsql;
            using (var ObjectCo = _fsql.Ado.MasterPool.Get())
            {
                var conn = ObjectCo.Value;
                var sheets = new List<ExportItem>();
                
                

                var tables = conn.Select<CksVirtualTable>().OrderBy(a=>a.Sequence).ToList();
                foreach (var table in tables)
                {
                    //查询列头信息，输出到sheet
                    int TableId = table.Id;
                    var columnQuery = conn.Select<CksVirtualColumn>().Where(a => a.TableId == TableId).OrderBy(a => a.Sequence);
                    var columns = columnQuery.ToList();
                    // 1. 先获取符合条件的批次数据（按时间范围过滤）
                    var batchQuery = conn.Select<CksCollected>()
                        .Where(x => x.TableId == table.Id)
                       .WhereIf(!parm.PrimaryCode.IsNullOrEmpty() && parm.PrimaryCode.Length >= 4, x => x.Suffix_4 == GetSuffix4(parm.PrimaryCode))
                     .WhereIf(!parm.PrimaryCode.IsNullOrEmpty(), x => x.PrimaryCode.EndsWith(parm.PrimaryCode))

                        .WhereIf(parm.BeginTime != null, x => x.CreatedTime >= parm.BeginTime.Value && x.CreatedTime <= parm.EndTime.Value)
                        .OrderByDescending(x => x.SequenceId).Take(100000);
                    var batches = batchQuery
                        .ToList();
                    // 2. 获取这些批次的所有明细数据（一次性加载）

                    var details = conn.Select<CksCollectedDetails>().FromQuery(batchQuery, columnQuery)//conn.Select<CksCollected>().WithMemory(batches))
                               .InnerJoin((a, b, c) => a.TableId == b.TableId && a.BatchSuffix == b.BatchSuffix && a.BatchCode == b.BatchCode)
                            .InnerJoin((a, b, c) => a.TableId == c.TableId && a.ColumnId == c.Id)

                                .Where(a=> a.t1.TableId == table.Id  )
                                .ToList((a, b, c) => new { 
                                        c.ColumnName, 
                                        b.CreatedTime, 
                                        b.SequenceId, 
                                        b.BatchCode, 
                                        a.Content }
                                ).Adapt<List<ExportObject>>();

                    //columns.Insert(0, new CksVirtualColumn() { ColumnName = "createdTime", Notes = "创建时间", LanguageKey = "" });
                    foreach (var item in columns)
                    {
                        string lowerColumnName = GetFirstLowerChar(item.ColumnName);
                        item.ColumnName = lowerColumnName;
                    }

                    //组装数据
                    var result = details
                                    .Select(d => new {
                                        d.BatchCode,
                                        d.ColumnName,
                                        d.Content,
                                        d.CreatedTime,
                                        d.SequenceId
                                    })
                                    .GroupBy(x => new { x.BatchCode, x.CreatedTime, x.SequenceId })
                                    .Select(g => {
                                        dynamic row = new ExpandoObject();
                                        var dict = (IDictionary<string, object>)row;
                                        //dict["批次"] = g.Key.BatchCode;
                                        dict["系统编号"] = g.Key.SequenceId;
                                        //  dict["CollectionTime"] = g.Key.CollectionTime;
                                        dict["创建时间"] = g.Key.CreatedTime;
                                        //遍历列头，为空则赋值空
                                        foreach (var col in columns)
                                        {
                                            var item = g.ToList().Find(a => a.ColumnName.ToLower() == col.ColumnName.ToLower());

                                            dict[$"{col.Notes}"] = item == null ? "" : item.Content;
                                        }
                                       
                                        return row;
                                    })
                                    .OrderByDescending(x => x.系统编号)
                                    .ToList();
                    if(details.Count == 0)
                    {
                        dynamic row = new ExpandoObject();
                        var dict = (IDictionary<string, object>)row;
                        dict["系统编号"] = "";
                        dict["创建时间"] = "";
                        foreach (var col in columns)
                        {
                            dict[$"{col.Notes}"] = "";
                        }
                        result = new List<dynamic>() { row };
                    }


                    sheets.Add(new ExportItem() { Name = table.TableName ,Data = result });


                }
                var exports = new Dictionary<string, object>();
                foreach (var item in sheets)
                {
                    exports[item.Name] = item.Data;
                }

                List<DynamicExcelColumn> excelColumns = new List<DynamicExcelColumn>() {
                     new DynamicExcelColumn("创建时间") {Name="创建时间", Format = "yyyy-MM-dd HH:mm:ss", Width = 20, Index = 0 },
                };
                var config = new OpenXmlConfiguration
                {

                    DynamicColumns = excelColumns.ToArray()
                };
                var stream = new MemoryStream();
                stream.SaveAs(exports, configuration: config);
                stream.Seek(0, SeekOrigin.Begin);

                Response.Headers.Append("Access-Control-Expose-Headers", "Content-Disposition");
                return File(stream,
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    HttpUtility.UrlEncode($"MES-Export-{DateTime.Now.ToString("MMddHHmmss")}.xlsx"));

            }




        }


        private string GetSuffix4(string content)
        {
            if (content.IsNullOrEmpty())
                return "";
            if (content.Length < 4)
                return "";
            return content.Substring(content.Length - 4);
        }
        private  string GetFirstLowerChar(string item)
        {
            return new string(item.Select((c, i) => i == 0 ? char.ToLower(c) : c).ToArray());
        }

        [HttpPost("GetVirtualTables")]
        public IActionResult GetVirtualTables()
        {
            var client = _PlcConfigService.AsSugarClient();
          var response =   client.Queryable<CksVirtualTable>().OrderBy(a => a.Sequence).ToList();
            return SUCCESS(response);
        }


        /// <summary>
        /// 查询PLC配置表详情
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        [HttpGet("{Id}")]
        [ActionPermissionFilter(Permission = "run:plcconfig:query")]
        public IActionResult GetPlcConfig(int Id)
        {
            var response = _PlcConfigService.GetInfo(Id);

            var info = response.Adapt<PlcConfigDto>();
            return SUCCESS(info);
        }

        /// <summary>
        /// 添加PLC配置表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ActionPermissionFilter(Permission = "run:plcconfig:add")]
        [Log(Title = "PLC配置表", BusinessType = BusinessType.INSERT)]
        public IActionResult AddPlcConfig([FromBody] PlcConfigDto parm)
        {
            var modal = parm.Adapt<PlcConfig>().ToCreate(HttpContext);

            var response = _PlcConfigService.AddPlcConfig(modal);

            return SUCCESS(response);
        }

        /// <summary>
        /// 更新PLC配置表
        /// </summary>
        /// <returns></returns>
        [HttpPut]
        [ActionPermissionFilter(Permission = "run:plcconfig:edit")]
        [Log(Title = "PLC配置表", BusinessType = BusinessType.UPDATE)]
        public IActionResult UpdatePlcConfig([FromBody] PlcConfigDto parm)
        {
            var modal = parm.Adapt<PlcConfig>().ToUpdate(HttpContext);
            var response = _PlcConfigService.UpdatePlcConfig(modal);

            return ToResponse(response);
        }

        /// <summary>
        /// 删除PLC配置表
        /// </summary>
        /// <returns></returns>
        [HttpDelete("delete/{ids}")]
        [ActionPermissionFilter(Permission = "run:plcconfig:delete")]
        [Log(Title = "PLC配置表", BusinessType = BusinessType.DELETE)]
        public IActionResult DeletePlcConfig([FromRoute] string ids)
        {
            var idArr = Tools.SplitAndConvert<int>(ids);

            return ToResponse(_PlcConfigService.Delete(idArr));
        }
    }
}